package org.example;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import javax.sql.DataSource;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class DatabaseManager {
    private static final Log log = LogFactory.getLog(DatabaseManager.class);
    
    private Connection connection;
    private DataSource dataSource;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void saveConfiguration(String key, String value) throws SQLException {
        try (Connection conn = dataSource.getConnection(); PreparedStatement pstmt = conn.prepareStatement("INSERT INTO config (key, value) VALUES (?, ?) ON DUPLICATE KEY UPDATE value = ?")) {

            pstmt.setString(1, key);
            pstmt.setString(2, value);
            pstmt.setString(3, value);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new SQLException("保存配置失败: " + e.getMessage(), e);
        }
    }

    public String loadConfiguration(String key) throws SQLException {
        try (Connection conn = dataSource.getConnection(); PreparedStatement pstmt = conn.prepareStatement("SELECT value FROM config WHERE key = ?")) {

            pstmt.setString(1, key);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("value");
                }
            }
        } catch (SQLException e) {
            throw new SQLException("加载配置失败: " + e.getMessage(), e);
        }
        return null;
    }

    public void connect(String dbType, String host, String port, String dbName, String user, String password) throws SQLException {
        String url = getDatabaseURL(dbType, host, port, dbName);
        connection = DriverManager.getConnection(url, user, password);
    }

    private String getDatabaseURL(String dbType, String host, String port, String dbName) {
        switch (dbType.toLowerCase()) {
            case "mysql":
                return "jdbc:mysql://" + host + ":" + port + "/" + dbName;
            case "postgresql":
                return "jdbc:postgresql://" + host + ":" + port + "/" + dbName;
            case "oracle":
                return "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName;
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
        }
    }

    public void createTemporaryTable(String tableName, Map<String, String> columns) throws SQLException {
        StringBuilder sql = new StringBuilder("CREATE TEMPORARY TABLE ").append(tableName).append(" (");
        for (Map.Entry<String, String> entry : columns.entrySet()) {
            sql.append(entry.getKey()).append(" ").append(entry.getValue()).append(", ");
        }
        sql.delete(sql.length() - 2, sql.length()).append(")");
        try (Statement stmt = connection.createStatement()) {
            stmt.executeUpdate(sql.toString());
        }
    }

    public void batchInsert(String tableName, List<Map<String, Object>> data) throws SQLException {
        log.info("开始批量插入数据，表名：" + tableName + ", 记录数：" + data.size());

        if (data.isEmpty()) {
            log.warn("没有数据需要插入，表名：" + tableName);
            return;
        }

        Map<String, Object> firstRow = data.get(0);
        String columns = String.join(", ", firstRow.keySet());
        String placeholders = firstRow.keySet().stream().map(k -> "?").collect(Collectors.joining(", "));
        String sql = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + placeholders + ")";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            for (Map<String, Object> row : data) {
                int i = 1;
                for (Object value : row.values()) {
                    pstmt.setObject(i++, value);
                }
                pstmt.addBatch();
            }

            // 检查每个批次的执行结果
            int[] results = pstmt.executeBatch();
            int successfulInserts = 0;
            for (int result : results) {
                if (result > 0) {
                    successfulInserts += result;
                } else if (result == Statement.EXECUTE_FAILED) {
                    log.warn("批次中某些语句执行失败");
                }
            }

            //log.info("批量插入完成，表名：" + tableName + ", 总记录数：" + data.size() + ", 成功插入：" + successfulInserts + "条记录");
            System.out.println("批量插入完成，表名：" + tableName + ", 总记录数：" + data.size() + ", 插入成功：" + successfulInserts + "条记录");
            // 如果成功插入的记录数与总记录数不符，则抛出异常
            if (successfulInserts != data.size()) {
                throw new SQLException("数据插入不完整，计划插入：" + data.size() + "条，实际成功：" + successfulInserts + "条");
            }
        } catch (SQLException e) {
            log.error("插入数据失败，表名：" + tableName + ", 数据量：" + data.size() + ", 错误信息：" + e.getMessage(), e);
            System.out.println("插入数据失败，表名：" + tableName + ", 数据量：" + data.size() + ", 错误信息：" + e.getMessage());
            throw new SQLException("插入数据失败: " + e.getMessage(), e);
        }
    }
    
    public List<String> getTables() throws SQLException {
        log.debug("获取数据库表列表");
        
        List<String> tables = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        String[] types = {"TABLE"};
        ResultSet rs = metaData.getTables(null, null, "%", types);
        while (rs.next()) {
            tables.add(rs.getString("TABLE_NAME"));
        }
        
        log.debug("获取数据库表列表完成，共" + tables.size() + "个表");
        return tables;
    }

    public List<String> getColumns(String tableName) throws SQLException {
        List<String> columns = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getColumns(null, null, tableName, "%");
        while (rs.next()) {
            columns.add(rs.getString("COLUMN_NAME"));
        }
        return columns;
    }

    public Map<String, String> getColumnsWithTypes(String tableName) throws SQLException {
        Map<String, String> columnTypeMap = new HashMap<>();
        try (ResultSet rs = connection.getMetaData().getColumns(null, null, tableName, null)) {

            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String typeName = rs.getString("TYPE_NAME");
                int dataType = rs.getInt("DATA_TYPE");

                // 根据数据库类型转换字段类型
                switch (dataType) {
                    case Types.INTEGER:
                    case Types.SMALLINT:
                    case Types.TINYINT:
                        columnTypeMap.put(columnName, "Integer");
                        break;
                    case Types.BIGINT:
                        columnTypeMap.put(columnName, "Long");
                        break;
                    case Types.FLOAT:
                    case Types.REAL:
                    case Types.DOUBLE:
                        columnTypeMap.put(columnName, "Double");
                        break;
                    case Types.DECIMAL:
                    case Types.NUMERIC:
                        columnTypeMap.put(columnName, "BigDecimal");
                        break;
                    case Types.DATE:
                        columnTypeMap.put(columnName, "LocalDate");
                        break;
                    case Types.TIMESTAMP:
                    case Types.TIME:
                        columnTypeMap.put(columnName, "LocalDateTime");
                        break;
                    case Types.VARCHAR:
                    case Types.CHAR:
                    case Types.LONGVARCHAR:
                        columnTypeMap.put(columnName, "String");
                        break;
                    // 可以根据需要添加更多类型映射
                    default:
                        columnTypeMap.put(columnName, "String");
                        break;
                }
            }
        } catch (SQLException e) {
            throw new SQLException("获取表字段类型失败: " + e.getMessage(), e);
        }
        return columnTypeMap;
    }

    /**
     * 根据外键条件查询目标值
     * @param table 外键表名
     * @param sourceColumn 外键源列
     * @param targetColumn 外键目标列
     * @param value 匹配值
     * @return 查询结果
     * @throws SQLException
     */
    public Object queryForeignKeyValue(String table, String sourceColumn, String targetColumn, Object value) throws SQLException {
        String sql = "SELECT " + targetColumn + " FROM " + table + " WHERE " + sourceColumn + " = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setObject(1, value);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getObject(targetColumn);
                }
            }

        }
        return null;
    }



    public Object queryForeignKeyLikeValue(String table, String sourceColumn, String targetColumn, Object value) throws SQLException {
        String sql = "SELECT " + targetColumn + " FROM " + table + " WHERE " + sourceColumn + " like ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setObject(1, "%" + value + "%");
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getObject(targetColumn);
                }
            }

        }
        return null;
    }


    /**
     * 根据外键条件查询目标记录的所有字段
     * @param table 外键表名
     * @param sourceColumn 外键源列
     * @param value 匹配值
     * @return 包含所有字段的记录
     * @throws SQLException
     */
    public Map<String, Object> queryForeignKeyRecord(String table, String sourceColumn, Object value) throws SQLException {
        String sql = "SELECT * FROM " + table + " WHERE " + sourceColumn + " = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setObject(1, value);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return getRowData(rs);
                }
            }
        }
        return null;
    }

    /**
     * 从结果集中获取所有字段数据
     * @param rs 结果集
     * @return 包含字段名和值的Map
     * @throws SQLException
     */
    private Map<String, Object> getRowData(ResultSet rs) throws SQLException {
        Map<String, Object> rowData = new HashMap<>();
        java.sql.ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            Object columnValue = rs.getObject(i);
            rowData.put(columnName, columnValue);
        }

        return rowData;
    }



    /**
     * 保存映射规则到数据库
     * @param tableName 表名
     * @param rules 映射规则列表
     */
//    public void saveMappingRules(String tableName, List<MappingRule> rules) {
//        String deleteSql = "DELETE FROM mapping_rules WHERE target_table = ?";
//        String insertSql = "INSERT INTO mapping_rules (source_field, target_field, conversion_type, enum_mapping, date_format, foreign_key_table, foreign_key_column, foreign_key_target_column, target_type, target_table) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//
//        try (Connection conn = getConnection();
//             PreparedStatement deleteStmt = conn.prepareStatement(deleteSql);
//             PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
//
//            // 删除旧记录
//            deleteStmt.setString(1, tableName);
//            deleteStmt.executeUpdate();
//
//            // 插入新记录
//            for (MappingRule rule : rules) {
//                insertStmt.setString(1, rule.getSourceField());
//                insertStmt.setString(2, rule.getTargetField());
//                insertStmt.setString(3, rule.getConversionType());
//                insertStmt.setString(4, rule.getEnumMapping());
//                insertStmt.setString(5, rule.getDateFormat());
//                insertStmt.setString(6, rule.getForeignKeyTable());
//                insertStmt.setString(7, rule.getForeignKeyColumn());
//                insertStmt.setString(8, rule.getForeignKeyTargetColumn());
//                insertStmt.setString(9, rule.getTargetType());
//                insertStmt.setString(10, rule.getTargetTable());
//                insertStmt.addBatch();
//            }
//
//            insertStmt.executeBatch();
//
//        } catch (SQLException e) {
//            System.err.println("保存映射规则失败: " + e.getMessage());
//        }
//    }

    /**
     * 根据目标表名获取映射配置
     * @param tableName 目标表名
     * @return 映射规则列表
     * @throws SQLException 如果查询失败
     */
    public List<MappingRule> getMappingRulesByTargetTable(String tableName) throws SQLException {
        if (connection == null || connection.isClosed()) {
            throw new SQLException("数据库连接未建立或已关闭");
        }
        
        List<MappingRule> rules = new ArrayList<>();
        String sql = "SELECT * FROM mapping_rules WHERE target_table = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            if (tableName == null || tableName.isEmpty()) {
                throw new IllegalArgumentException("表名不能为空");
            }
            
            pstmt.setString(1, tableName);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    MappingRule rule = new MappingRule();
                    rule.setSourceField(rs.getString("source_field"));
                    rule.setTargetField(rs.getString("target_field"));
                    rule.setConversionType(rs.getString("conversion_type"));
                    rule.setEnumMapping(rs.getString("enum_mapping"));
                    rule.setDateFormat(rs.getString("date_format"));
                    rule.setForeignKeyTable(rs.getString("foreign_key_table"));
                    rule.setForeignKeyColumn(rs.getString("foreign_key_column"));
                    rule.setForeignKeyTargetColumn(rs.getString("foreign_key_target_column"));
                    //rule.setTargetType(rs.getString("target_type"));
                    rule.setTargetTable(rs.getString("target_table"));
                    rule.setFilePath(rs.getString("file_path"));
                    rule.setDefaultValue(rs.getString("default_value"));
                    rules.add(rule);
                }
            }
        } catch (SQLException e) {
            throw new SQLException("查询映射规则失败: " + e.getMessage(), e);
        }
        
        return rules;
    }

    public void close() throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }

    public Connection getConnection() {
        return connection;
    }
}